![[Redshift] STL_INSERTとSTL_QUERYを使用して過去に実行したクエリの情報を調べる](https://devio2023-media.developers.io/wp-content/uploads/2014/05/Amazon_Redshift.png)
[Redshift] STL_INSERTとSTL_QUERYを使用して過去に実行したクエリの情報を調べる
はじめに
SQLの作成や修正する際に、過去のクエリの実行状況を確認したい事が有ります。Redshiftではクラスターを起動してから終了するまでのクエリをコンソールから見る事ができますが、スナップショットに保存ができなかったり、保存期間が短かったりします。 Redshiftには実行したクエリを自動的に保存してくれるテーブルが有るので活用してみたいと思います。
環境
MacOSX 10.10.5 Yosemite Redshift 1.0.1125
使用するSTLテーブル
STL_INSERT
| 列名 | データ型 | 説明 |
|---|---|---|
| userid | integer | エントリを生成したユーザーのID。 |
| query | integer | クエリ ID。クエリ列は、他の各種システムテーブルおよびビューを結合するために使用可能。 |
| slice | integer | クエリが実行されているスライスを識別する番号。 |
| segment | integer | クエリセグメントを識別する番号。 |
| step | integer | 実行されたクエリステップ。 |
| starttime | timestamp | UTCで表されたクエリの実行開始時刻。秒の小数部の精度（6 桁）を使用。 |
| endtime | timestamp | UTC で表されたクエリの実行終了時刻。秒の小数部の精度（6 桁）を使用。 |
| tasknum | integer | ステップの実行に割り当てられたクエリタスクプロセスの数。 |
| rows | bigint | 処理された合計行数。 |
| tbl | integer | テーブル ID。 |
STL_QUERY
| 列名 | データ型 | 説明 |
|---|---|---|
| userid | integer | エントリを生成したユーザーの ID。 |
| query | integer | クエリ ID。クエリ列は、他の各種システムテーブルおよびビューを結合するために使用可能。 |
| label | character(15) | クエリを実行するために使用される名前、またはSET QUERY_GROUPコマンドによって定義されるラベル。クエリがファイルベースでないか、QUERY_GROUPパラメーターが設定されていない場合、このフィールドの値はdefault。 |
| xid | bigint | トランザクション ID。 |
| pid | integer | プロセス ID。通常、セッション内のすべてのクエリは同一プロセスで実行されるため、一連のクエリを同一セッションで実行した場合、この値は通常は一定です。Amazon Redshiftは特定の内部イベントに続いてアクティブなセッションを再起動し、新しい PID を割り当てる場合がある。 |
| database | character(32) | クエリが発行されたときにユーザーが接続されたデータベースの名前。 |
| querytxt | character(4000) | クエリの実際のクエリテキスト。 |
| starttime | timestamp | UTCで表されたクエリの実行開始時刻。秒の小数部の精度（6 桁）を使用。 |
| endtime | timestamp | UTCで表されたクエリの実行終了時刻。秒の小数部の精度（6 桁）を使用。 |
| aborted | integer | クエリがシステムによって中断されたかユーザーによってキャンセルされた場合、この列は 1 になります。クエリが（クライアントに結果を返すことも含めて）最後まで実行された場合、この列は 0 になります。クライアントが結果を受け取る前に接続を解除した場合、クエリはバックエンドで正常に完了した場合でも、キャンセルされたものとしてマーク（1）されます。 |
| insert_pristine | integer | 現在のクエリの実行中に書き込みクエリが実行可能であるかどうか。1 = 書き込みクエリは許可されません。0 = 書き込みクエリは許可されます。この列は、デバッグで使用することが意図されています。 |
使ってみる
目的
ユーザーごとのクエリの開始時間、終了時間、行数、クエリを表示する。
確認
事前に以下のクエリを上から順に実行してから確認しました。 ・DROP TABLE ・CREATE TABLE ・INSERT INTO ・COPY ・DELETE ・TRUNCATE
STLテーブルの結合
SELECT
I.userid
, I.query
, convert_timezone('JST',Q.starttime) AS starttime
, convert_timezone('JST',Q.endtime) AS endtime
, I.rows
, Q.querytxt
FROM stl_insert AS I
LEFT OUTER JOIN stl_query AS Q ON I.query = Q.query
WHERE Q.aborted = 0
AND Q."database" = 'testdb'
ORDER BY Q.starttime DESC;
;
userid | query | starttime | endtime | rows | querytxt
--------+-------+----------------------------+----------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------
100 | 1391 | 2016-11-29 22:15:37.349719 | 2016-11-29 22:15:39.832749 | 2 | COPY blog.check_table FROM 's3://bucket/folder/check_table.tsv' CREDENTIALS '' DELIMITER '\\t' REMOVEQUOTES NULL AS '' IGNOREHEADER 1
100 | 1391 | 2016-11-29 22:15:37.349719 | 2016-11-29 22:15:39.832749 | 1 | COPY blog.check_table FROM 's3://bucket/folder/check_table.tsv' CREDENTIALS '' DELIMITER '\\t' REMOVEQUOTES NULL AS '' IGNOREHEADER 1
100 | 1389 | 2016-11-29 22:15:34.685304 | 2016-11-29 22:15:36.129773 | 2 | INSERT INTO blog.check_table VALUES (1,'aaa'),(2,'bbb'),(3,'ccc')
100 | 1389 | 2016-11-29 22:15:34.685304 | 2016-11-29 22:15:36.129773 | 1 | INSERT INTO blog.check_table VALUES (1,'aaa'),(2,'bbb'),(3,'ccc')
(4 rows)
COPYとINSERTのみが登録されている事が確認できました。 COPYのCREDENTIALは自動的に空欄になる様です。
さいごに
とても便利でありがたいテーブルですので、他のSTLテーブルも探ってみたいと思います。
